package com.example.bysj.Dao;
//201902104061徐中堂
import com.example.bysj.domain.*;
import com.example.bysj.Service.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
//声明数据库访问类
@Repository("graduateProjectDao")
public class GraduateProjectDao {
    //自动注入
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Autowired
    private GraduateProjectCategoryService graduateProjectCategoryService;
    @Autowired
    private GraduateProjectSourceService graduateProjectSourceService;
    @Autowired
    private GraduateProjectStatusService graduateProjectStatusService;
    @Autowired
    private GraduateProjectTruenessService graduateProjectTruenessService;
    @Autowired
    private GraduateProjectTypeService graduateProjectTypeService;
    @Autowired
    private TeacherService teacherService;
    @Autowired
    private StudentService studentService;
    //增加
    public boolean add(GraduateProject graduateProject) {
        //sql语句
        String sql = "INSERT INTO GraduateProject (title,score,graduateProjectCategory_id," +
                "graduateProjectType_id,graduateProjectStatus_id,graduateProjectSource_id,graduateProjectTrueness_id," +
                "teacher_id,student_id,year) VALUES (?,?,?,?,?,?,?,?,?,?)";
        //执行sql语句
        int affectedRowNum = jdbcTemplate.update(sql, graduateProject.getTitle(),
                //获取各个字段，根据id进行赋值
                graduateProject.getScore(),graduateProject.getGraduateProjectCategory().getId(),
                graduateProject.getGraduateProjectType().getId(),graduateProject.getGraduateProjectStatus().getId(),
                graduateProject.getGraduateProjectSource().getId(),graduateProject.getGraduateProjectTrueness().getId()
                ,graduateProject.getTeacher().getId(),graduateProject.getStudent().getId(),graduateProject.getYear());
        if (affectedRowNum != 1) {
            return false;
        } else {
            return true;
        }
    }
    //通过定义内部类来返回查询结果
    //SQL查询出来的数据列名就是和实体类的属性名不一样，或者想按照自己的规则来装配实体类，那么就可以定义并使用自己的Row Mapper。
    public class GraduateProjectRowMapper implements RowMapper<GraduateProject> {
        @Override
        public GraduateProject mapRow(ResultSet resultSet, int i) throws SQLException {
            //将查询结果赋值给对象
            GraduateProject graduateProject = new GraduateProject();
            graduateProject.setId(resultSet.getInt("id"));
            graduateProject.setTitle(resultSet.getString("title"));
            graduateProject.setScore(resultSet.getDouble("score"));

            GraduateProjectCategory graduateProjectCategory =
                    graduateProjectCategoryService.find (resultSet.getInt("graduateProjectCategory_id"));
            graduateProject.setGraduateProjectCategory(graduateProjectCategory);

            GraduateProjectType graduateProjectType =
                    graduateProjectTypeService.find(resultSet.getInt("graduateProjectType_id"));
            graduateProject.setGraduateProjectType(graduateProjectType);

            GraduateProjectStatus graduateProjectStatus =
            graduateProjectStatusService.find(resultSet.getInt("graduateProjectStatus_id"));
            graduateProject.setGraduateProjectStatus(graduateProjectStatus);

            GraduateProjectSource graduateProjectSource =
            graduateProjectSourceService.find(resultSet.getInt("graduateProjectSource_id"));
            graduateProject.setGraduateProjectSource(graduateProjectSource);

            GraduateProjectTrueness graduateProjectTrueness =
            graduateProjectTruenessService.find(resultSet.getInt("graduateProjectTrueness_id"));
            graduateProject.setGraduateProjectTrueness(graduateProjectTrueness);
            Teacher teacher =teacherService.find(resultSet.getInt("teacher_id"));
            graduateProject.setTeacher(teacher);
            Student student = studentService.getOneById(resultSet.getInt("student_id"));
            graduateProject.setStudent(student);
            graduateProject.setYear(resultSet.getInt("year"));

            return graduateProject;
        }
    }
    //根据id查询
    public GraduateProject find(int id){
        String sql = "SELECT * FROM GraduateProject WHERE id = ?";
        List<GraduateProject> graduateProjects = jdbcTemplate.query(sql, new Object[]{id}, new GraduateProjectRowMapper());
        if(!graduateProjects.isEmpty()){
            return graduateProjects.get(0);
        } else
            return null;
    }
    //查询全部
    public List<GraduateProject> findAll(){
        String sql = "SELECT * FROM GraduateProject ";
        List<GraduateProject> graduateProjects = jdbcTemplate.query(sql, new GraduateProjectRowMapper());
        return graduateProjects;
    }
    //修改
    public boolean update(GraduateProject graduateProject) {
        String sql = "UPDATE GraduateProject SET " +
                "title=?,score=?,year=?," +
                "student_id=?,teacher_id=?," +
                "graduateProjectTrueness_id=?,GraduateProjectStatus_id=?," +
                "graduateProjectSource_id=?,graduateProjectCategory_id=?,graduateProjectType_id=? " +
                "WHERE id=?";
        //获取各字段为其赋值
        int rowAffected = jdbcTemplate.update(sql,
                graduateProject.getTitle(),
                graduateProject.getScore(),
                graduateProject.getYear(),
                graduateProject.getStudent().getId(),
                graduateProject.getTeacher().getId(),
                graduateProject.getGraduateProjectTrueness().getId(),
                graduateProject.getGraduateProjectStatus().getId(),
                graduateProject.getGraduateProjectSource().getId(),
                graduateProject.getGraduateProjectCategory().getId(),
                graduateProject.getGraduateProjectType().getId(),
                graduateProject.getId());
        //受影响的行数
        if (rowAffected!=1){
            return false;
        } else {
            return true;
        }
    }

    //根据id删除
    public boolean delete(int id){
        String sql = "DELETE FROM graduateProject WHERE id = ? ";
        int affectedRowNum = jdbcTemplate.update(sql,id);
        if (affectedRowNum!=1){
            return false;
        }else{
            return true;
        }
    }
}
